﻿Create proc themlich
@Ngay datetime,
@Chuyen char(15),
@So char(15),
@loai char(15),
@sotai int,
@solo int
As
Begin
if (not exists (select * from CHUYENXE where MACHUYEN = @Chuyen))
	begin
		print 'Mã chuyến ' +@chuyen + ' Khong Ton Tai'
		return
	end
if (exists (select * from LICHDI where @Ngay=NGAYDI AND @Chuyen=MACHUYEN AND @SO=SOHIEU AND @loai=MALOAI))
	begin
		print 'Lich di nay da co'
		return
	end
if (not exists (select * from LOAIXE where MALOAI = @loai))
	begin
		print 'Loại xe ' +@loai + ' Khong Ton Tai'
		return
	end
if (not exists (select SOHIEU from XE where MALOAI=@loai AND SOHIEU=@So))
	begin
		print ' hieu xe ko co'
		return
	end
insert into LICHDI values(@Ngay,@Chuyen,@So,@loai,@sotai,@solo,'0','NO')
END 

exec them '12/12/2013','991','11','A002','2','2'
-----------------------------------------------------------------------------------
alter proc test
@chuyen nchar(20),
@ngaybd varchar(20),
@ngaykt varchar(20),
@tong int output
As
begin try
	if(not exists( select * from LICHDI where MACHUYEN=@chuyen and convert(varchar(20),NGAYDI,103) between @ngaybd and @ngaykt))
	begin
		print N'Khong ton tai chuyen xe co ma la '+@chuyen
		return
	end
		set @tong= (select count(MACHUYEN) from LICHDI where MACHUYEN=@chuyen and convert(varchar(20),NGAYDI,103)>= @ngaybd and Convert(varchar(20),NGAYDI,103) <=@ngaykt)
		select * from LICHDI where MACHUYEN=@chuyen and convert(varchar(20),NGAYDI,103)>= @ngaybd and Convert(varchar(20),NGAYDI,103) <=@ngaykt
	 
end try
BEGIN CATCH 
			DECLARE @ErrorMsg VARCHAR(2000)
			SELECT @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
			RAISERROR(@ErrorMsg, 16,1)
			ROLLBACK TRAN
			RETURN
END CATCH 

declare @tong1 int
exec test 449,'21/11/2013','22/11/2013',@tong1 output
print @tong1

select * from LICHDI where NGAYDI between 